故障分析|数据库表空间被rm后，怎么处理

背景介绍

客户生产环境由于运维人员误操作，将 MySQL 数据库中，某个业务表 ibd 文件给 rm 掉了。由于历史原因该环境没有可用从库。针对这种情况，我们怎么处理呢？

情景复现

前提条件

• 创建一个 MySQL-5.7.28 的单实例数据库，并用 sysbench 准备1张100w的测试表。

[root@localhost ~]# sysbench --mysql-host=10.186.65.84 --mysql-port=8484 --mysql-user=xiao --mysql-password=xiao --mysql-db=test --db-driver=mysql --mysql_storage_engine=innodb --db-ps-mode=disable --test=/usr/local/sysbench1.0/share/sysbench/oltp_insert.lua --table_size=1000000 --threads=1 --tables=1 --db-ps-mode=disable --percentile=95 --report-interval=1 --time=1 prepareWARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)Creating table 'sbtest1'...Inserting 1000000 records into 'sbtest1'Creating a secondary index on 'sbtest1'...[root@localhost ~]#

• 查看数据库中数据：

[root@localhost ~]# mysql -uroot -p -S data/mysql/data/8484/mysqld.sockEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 6Server version: 5.7.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respective owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select count(*) from sbtest1;+----------+| count(*) |+----------+| 1000000 |+----------+1 row in set (0.21 sec)

• 查看sbtest1表的ibd文件：

[root@localhost test]# ll -h data/mysql/data/8484/test/total 241M-rw-r----- 1 actiontech-mysql actiontech-mysql 60 Jun 7 07:55 db.opt-rw-r----- 1 actiontech-mysql actiontech-mysql 8.5K Jun 7 07:57 sbtest1.frm-rw-r----- 1 actiontech-mysql actiontech-mysql 240M Jun 7 07:57 sbtest1.ibd

开展测试

通过 sysbench 对数据库开展读写测试，持续5分钟，期间对 sbtest1.ibd 文件进行 rm 操作，观测数据库运行状态：

sysbench 下压力

[root@localhost ~]# sysbench --mysql-host=10.186.65.84 --mysql-port=8484 --mysql-user=xiao --mysql-password=xiao --mysql-db=test --db-driver=mysql --mysql_storage_engine=innodb --db-ps-mode=disable --test=/usr/local/sysbench1.0/share/sysbench/oltp_read_write.lua --table_size=1000000 --threads=1 --tables=1 --db-ps-mode=disable --percentile=95 --report-interval=20 --time=300 runWARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2)Running the test with following options:Number of threads: 1Report intermediate results every 20 second(s)Initializing random number generator from current timeInitializing worker threads...Threads started![ 20s ] thds: 1 tps: 79.34 qps: 1587.51 (r/w/o: 1111.43/317.35/158.73) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00[ 40s ] thds: 1 tps: 80.35 qps: 1607.27 (r/w/o: 1124.95/321.61/160.71) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00[ 60s ] thds: 1 tps: 80.35 qps: 1606.98 (r/w/o: 1124.92/321.36/160.70) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00[ 80s ] thds: 1 tps: 77.90 qps: 1557.65 (r/w/o: 1090.40/311.45/155.80) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00[ 100s ] thds: 1 tps: 79.75 qps: 1595.13 (r/w/o: 1116.64/319.00/159.50) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00[ 120s ] thds: 1 tps: 80.90 qps: 1617.36 (r/w/o: 1131.96/323.60/161.80) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00[ 140s ] thds: 1 tps: 81.40 qps: 1628.40 (r/w/o: 1140.00/325.60/162.80) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00[ 160s ] thds: 1 tps: 80.80 qps: 1615.68 (r/w/o: 1130.89/323.20/161.60) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00

删除 ibd 文件

[root@localhost test]# ll -h data/mysql/data/8484/test/total 241M-rw-r----- 1 actiontech-mysql actiontech-mysql 60 Jun 7 07:55 db.opt-rw-r----- 1 actiontech-mysql actiontech-mysql 8.5K Jun 7 07:57 sbtest1.frm-rw-r----- 1 actiontech-mysql actiontech-mysql 240M Jun 7 07:57 sbtest1.ibd[root@localhost test]# [root@localhost test]# [root@localhost test]# [root@localhost test]# rm -r sbtest1.ibd rm: remove regular file ‘sbtest1.ibd’? y[root@localhost test]# ll -h data/mysql/data/8484/test/total 16K-rw-r----- 1 actiontech-mysql actiontech-mysql 60 Jun 7 07:55 db.opt-rw-r----- 1 actiontech-mysql actiontech-mysql 8.5K Jun 7 07:57 sbtest1.frm[root@localhost test]#

查看 mysql-error.log 与 sysbench 压测结果

查看 sysbench 结果：

查看 mysql-error.log ：

可以看到当 sbtest1.ibd 文件被 rm 掉后，针对该表的增删改查操作还是能够正常进行，且 mysql-error.log 中没有任何报错。

接下来测试，将删除文件从 /proc/`pidof mysqld`/fd 中复制文件到原来位置，查看 MySQL 能正常重启。

文件恢复

通过 lsof 查看 sbtest1.ibd 文件状态

[root@localhost 8484]# lsof |grep sbtest1.ibd

其中红框处是 FD ：文件描述符，应用程序通过文件描述符识别该文件。

[root@localhost 8484]# ls -l proc/1933/fd/78lrwx------ 1 actiontech-mysql actiontech-mysql 64 Jun 7 08:22 proc/1933/fd/78 -> data/mysql/data/8484/test/sbtest1.ibd (deleted)

在进行cp之前，要确保该表数据变更已经落盘，且没有新的操作。

[root@localhost 8484]# cp proc/1933/fd/78 data/mysql/data/8484/test/sbtest1.ibd

修改文件属性

[root@localhost test]# chown actiontech-mysql:actiontech-mysql sbtest1.ibd[root@localhost test]# ll -htotal 497M-rw-r----- 1 actiontech-mysql actiontech-mysql 60 Jun 7 07:55 db.opt-rw-r----- 1 actiontech-mysql actiontech-mysql 8.5K Jun 7 07:57 sbtest1.frm-rw-r----- 1 actiontech-mysql actiontech-mysql 248M Jun 7 08:35 sbtest1.ibd

重启 mysql

[root@localhost test]# systemctl restart mysqld_8484.service[root@localhost test]#

查看 mysql-error.log:

通过 sysbench 验证文件是否可用

[root@localhost ~]# sysbench --mysql-host=10.186.65.84 --mysql-port=8484 --mysql-user=xiao --mysql-password=xiao --mysql-db=test --db-driver=mysql --mysql_storage_engine=innodb --db-ps-mode=disable --test=/usr/local/sysbench1.0/share/sysbench/oltp_read_write.lua --table_size=1000000 --threads=1 --tables=1 --db-ps-mode=disable --percentile=95 --report-interval=1 --time=30 run

可以看到将 ibd 文件恢复后，能够正常读写。

哪些操作能让我们知道表 ibd 文件不存在了呢？

mysql> alter table sbtest1 add column name varchar(15);ERROR 1030 (HY000): Got error 44 from storage engine

此时对表进行 select 操作，依然能够查看结果：

mysql> select count(*) from sbtest1;+-------------+| count(*) |+-------------+| 1000000 |+-------------+1 row in set (0.20 sec)

查看 mysql-error.log 信息：

[ERROR] InnoDB: Cannot rename './test/sbtest1.ibd' to './test/#sql-ib134-2944391225.ibd' for space ID 118 because the source file does not existmysql> alter table sbtest1 drop column pad;ERROR 1030 (HY000): Got error 44 from storage engine

此时对表进行 select 操作，依然能够查看结果：

mysql> select count(*) from sbtest1;+----------+| count(*) |+----------+| 1000000 |+----------+1 row in set (0.19 sec)

查看 mysql-error.log 信息：

[ERROR] InnoDB: Cannot rename './test/sbtest1.ibd' to './test/#sql-ib135-2944391227.ibd' for space ID 118 because the source file does not exist.

2、给表新增/删除索引---不能发现

mysql> alter table sbtest1 add index idx_pad(pad);Query OK, 0 rows affected (6.13 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table sbtest1\G*************************** 1. row *************************** Table: sbtest1Create Table: CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '', `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`), KEY `idx_pad` (`pad`)) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.00 sec)mysql> alter table sbtest1 drop index idx_pad;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table sbtest1\G*************************** 1. row *************************** Table: sbtest1Create Table: CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '', `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`)) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin1 row in set (0.00 sec)

3、对表进行 OPTIMIZE 操作---能发现

mysql> OPTIMIZE table sbtest1;+-----------------+------------+---------------+--------------------------------------------------------------------------------------+| Table | Op | Msg_type | Msg_text |+-----------------+-------------+---------------+--------------------------------------------------------------------------------------+| test.sbtest1 | optimize | note | Table does not support optimize, doing recreate + analyze instead || test.sbtest1 | optimize | error | Got error 44 from storage engine || test.sbtest1 | optimize | status | Operation failed |+-----------------+-------------+----------+---------------------------------------------------------------------------------------------+3 rows in set, 1 warning (12.30 sec)

查看 mysql-error.log ：

[ERROR] InnoDB: Cannot rename './test/sbtest1.ibd' to './test/#sql-ib136-2944391229.ibd' for space ID 118 because the source file does not exist.

4、对表进行 ALTER TABLE tbl_name ENGINE=INNODB 操作---能发现

mysql> ALTER TABLE sbtest1 ENGINE=INNODB;ERROR 1030 (HY000): Got error 44 from storage engine


查看 mysql-error.log ：

[ERROR] InnoDB: Cannot rename './test/sbtest1.ibd' to './test/#sql-ib137-2944391231.ibd' for space ID 118 because the source file does not exist.


5、对表字段属性进行变更操作---能发现

mysql> alter table sbtest1 modify column pad char(70);
ERROR 1025 (HY000): Error on rename of './test/sbtest1' to './test/#sql2-5bc4-7' (errno: 197 - Tablespace cannot be accessed)


查看 mysql-error.log ：

[ERROR] InnoDB: Cannot rename './test/sbtest1.ibd' to './test/#sql2-5bc4-7.ibd' for space ID 118 because the source file does not exist.